﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
namespace BusinessLogicLayer
{
    public class RetrivalListFacade
    {
        InventorySoreDataContext context;
        RetrivalList retrivallist;
        RetrivalListDetail retrivallistdetail;
        public RetrivalListFacade()
        {
            context = new InventorySoreDataContext();
        }
        public IQueryable FindAll()
        {
            var retrivallists = from rlist in context.RetrivalLists select rlist;
            return retrivallists;
        }
        public RetrivalList FindRetrivalListByID(string retrivalListID)
        {
            var retrivallist = (from rlist in context.RetrivalLists where rlist.RetrivalListID == retrivalListID select rlist).Single();
            return retrivallist;
        }
        public IQueryable FindRetrivalListByStatus(string status)
        {
            var retrivallists = from rlist in context.RetrivalLists where rlist.Status == status select rlist;
            return retrivallists;
        }
        public IQueryable FindRetrivalListDetailsByID(string retrivalListID)
        {
            var retrivallistdetails = (from rdlist in context.RetrivalListDetails
                                       where rdlist.RetrivalListID == retrivalListID
                                       select new
                                       {
                                           Description = rdlist.Item.Description,
                                           OrderedQty = rdlist.NeededQty,
                                           DeliveredQty = rdlist.ActualQty,
                                           CollectedQty = rdlist.CollectedQty,
                                       }
                                       );
            return retrivallistdetails;
        }
        public string FindFullFilledDateByID(string retrivalListID)
        {
            try
            {
                var fDate = (from retrivalList in context.RetrivalLists where retrivallist.RetrivalListID == retrivalListID select new { LastModified = retrivallist.LastModified }).Single();
                return Convert.ToString(fDate);
            }
            catch (Exception e)
            {
                return null;
            }

        }
        public RetrivalListDetail FindRetrivalListDetailsByIDAndItemCode(string retrivalListID, string itemCode)
        {
            var retrivallistdetail = (from rdlist in context.RetrivalListDetails where rdlist.RetrivalListID == retrivalListID && rdlist.ItemCode == itemCode select rdlist).SingleOrDefault();
            return retrivallistdetail;
        }

        public void InsertRetrivalList(RetrivalList r)
        {
            context.RetrivalLists.InsertOnSubmit(r);
            context.SubmitChanges();
        }
        public void InsertRetrivalListDetails(RetrivalListDetail rd)
        {
            context.RetrivalListDetails.InsertOnSubmit(rd);
            context.SubmitChanges();
        }
        public void InsertRetrivalListDetail(List<RetrivalListDetail> rdlist)
        {
           
            context.RetrivalListDetails.InsertAllOnSubmit(rdlist);
            context.SubmitChanges();
        }
        public IQueryable FindRetrievalDetails(string retrievalID)
        {
            var details = from detail in context.RetrivalListDetails where detail.RetrivalListID == retrievalID select detail;
            return details;
        }
        public void UpdateStatus(string retrivalListID, string status)
        {
            try
            {
                retrivallist = FindRetrivalListByID(retrivalListID);
                retrivallist.Status = status;
                context.SubmitChanges();
            }
            catch (Exception ee)
            {
                throw new Exception("Status cannot be updated successfully!");
            }
        }
        public void UpdateBlackList(string retrivalListID)
        {
            try
            {
                retrivallist = FindRetrivalListByID(retrivalListID);
                retrivallist.Department.PriorityID = "4";
                context.SubmitChanges();
            }

            catch (Exception ee)
            {
                throw new Exception("Status cannot be updated successfully!");
            }
        }
        public void UpdateLastModified(string retrivalListID, DateTime lastModified)
        {
            retrivallist = FindRetrivalListByID(retrivalListID);
            retrivallist.LastModified = lastModified;
            context.SubmitChanges();
        }
        public void UpdateActualQty(string retrivalListID, string itemCode, int actualQty)
        {
            retrivallistdetail = FindRetrivalListDetailsByIDAndItemCode(retrivalListID, itemCode);
            retrivallistdetail.ActualQty = actualQty;
            retrivallistdetail.Remark = "Processed";
            retrivallistdetail.RetrivalList.Status = "Processed";
            context.SubmitChanges();
        }
        public void UpdateRemark(string retrivalListID, string itemCode, string remark)
        {
            try
            {
                retrivallistdetail = FindRetrivalListDetailsByIDAndItemCode(retrivalListID, itemCode);
                retrivallistdetail.Remark = remark;
                context.SubmitChanges();
            }

            catch (Exception ee)
            {
                throw new Exception("Update is not successful!");
            }
        }
        public IQueryable GetRequest(string employeeID)
        {
            var query = (from retrival in context.RetrivalListDetails
                         where retrival.RetrivalList.Department.ClerkID == employeeID && retrival.RetrivalList.Status == "Approved"
                         group retrival by new { retrival.ItemCode, retrival.Item.Description, retrival.NeededQty, retrival.Item.Stock, retrival.Item.ReorderLevel, retrival.Item.LogicStock } into retrivalDetails
                         select new
                         {
                             ItemCode = retrivalDetails.Key.ItemCode,
                             Description = retrivalDetails.Key.Description,
                             OrderedQty = retrivalDetails.Sum(s => s.NeededQty),
                             QtyInHand = retrivalDetails.Key.Stock,
                             ReorderLevel = retrivalDetails.Key.ReorderLevel,
                             LogicStock = retrivalDetails.Key.LogicStock,
                         }).Distinct();

            return query;
        }
        public IQueryable FindRetrivalListByDept(string departmentCode)
        {
            var query = from retList in context.RetrivalLists
                        where retList.Departmentcode == departmentCode
                        select new
                        {
                            DisbursementNo = retList.RetrivalListID,
                            DateModified = retList.LastModified,
                        };
            return query;
        }

        public IQueryable GetDisbursementDetails(string departmentCode)
        {
            var query = (from retrivalDetails in context.RetrivalListDetails
                         where retrivalDetails.RetrivalList.Departmentcode == departmentCode && retrivalDetails.RetrivalList.Status == "Processed"
                         select new
                         {
                             Description = retrivalDetails.Item.Description,
                             OrderedQty = Convert.ToString(retrivalDetails.NeededQty),
                             ActualQty = Convert.ToString(retrivalDetails.ActualQty),

                         });
            return query;

        }
        public IQueryable FindDisbursementList(string clerkID)
        {
            //string departmentCode=null;
            //DepartmentFacade department = new DepartmentFacade();
            //var departments=department.FindDepartmentByClerk(clerkID);
            //foreach (Department depart in departments)
            //{
            //    departmentCode = depart.DepartmentCode;
            //}
            try
            {
                var disbursements = from dis in context.RetrivalLists
                                    where dis.Department.ClerkID == clerkID && dis.Status == "Processed"
                                    select new
                                    {
                                        DisbursementNo = dis.RetrivalListID,
                                        DepartmentName = dis.Department.DepartmentName,
                                    };
                return disbursements;
            }

            catch (Exception ee)
            {
                return null;
            }
        }

        public IQueryable FindRetrievalDetailsByListID(string listID)
        {
            try
            {
                var disDetails = from dis in context.RetrivalListDetails
                                 where dis.RetrivalListID == listID
                                 select new
                                 {
                                     ItemCode = dis.ItemCode,
                                     ItemDescription = dis.Item.Description,
                                     DepartmentName = dis.RetrivalList.Department.DepartmentName,
                                     ActualQty = dis.ActualQty,
                                 };
                return disDetails;
            }

            catch (Exception ee)
            {
                return null;
            }
        }

        public void UpdateCollectQty(string retrivalListID, string itemCode, int qty)
        {
            try
            {
                retrivallistdetail = FindRetrivalListDetailsByIDAndItemCode(retrivalListID, itemCode);
                retrivallistdetail.CollectedQty = qty;
                context.SubmitChanges();
            }

            catch (Exception ee)
            {
                throw new Exception("Update is not successful!");
            }
        }

        public void UpdateNeededQty(string retrivalID, string itmCode, int needQty)
        {
            retrivallistdetail = FindRetrivalListDetailsByIDAndItemCode(retrivalID, itmCode);
            retrivallistdetail.NeededQty = retrivallistdetail.NeededQty + needQty;
            context.SubmitChanges();
        }

        public string GenerateRetrievalListID(string departmentCode)
        {
            string listID = null;
            if (Convert.ToInt32(DateTime.Now.DayOfWeek) != 5)
            {
             
                var query1 = (from retrival in context.RetrivalLists
                              where retrival.Departmentcode == departmentCode && retrival.Status == "Approved"
                              select retrival).SingleOrDefault();
                if (query1 != null)
                {
                    listID = query1.RetrivalListID;
                    return listID;
                }
                else
                {
                    int query2 = (from retrieval in context.RetrivalLists
                                  select retrieval).Count();
                    query2++;
                    listID = "A00" + query2;
                    RetrivalList rtv = new RetrivalList();
                    rtv.RetrivalListID = listID;
                    rtv.Departmentcode = departmentCode;
                    rtv.LastModified = DateTime.Now;
                    rtv.Status = "Approved";
                    InsertRetrivalList(rtv);
                }
            }
            else
            {
                int query1 = (from retrieval in context.RetrivalLists
                              select retrieval).Count();
                query1++;
                listID = "A00" + query1;
                RetrivalList rtv = new RetrivalList();
                rtv.RetrivalListID = listID;
                rtv.Departmentcode = departmentCode;
                rtv.LastModified = DateTime.Now;
                rtv.Status = "Approved";
                InsertRetrivalList(rtv);
            }
            return listID;
        }

        public bool CheckRetrivalDetailId(string retrivalId, string itmCode)
        {
            bool flag = false;
            RetrivalListDetail rdl = FindRetrivalListDetailsByIDAndItemCode(retrivalId, itmCode);
            if (rdl != null)
            {
                flag = true;
            }
            return flag;
        }

        public void InsertRetrivalList(RetrivalList retrivalList, List<RetrivalListDetail> retrivalListDetail)
        {
            try
            {

                context.RetrivalLists.InsertOnSubmit(retrivalList);
                context.RetrivalListDetails.InsertAllOnSubmit(retrivalListDetail);
                context.SubmitChanges();
            }

            catch (Exception ee)
            {
                throw new Exception("Data is already existed!");
            }
        }

       
    }
}
